 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spBulkOrdersIDSReport]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [spBulkOrdersIDSReport]
GO

	
	
CREATE PROCEDURE spBulkOrdersIDSReport
	@conPointID int,	
	@idsSort int,
	@quantity int,
	@IsUSUser bit,
	@OwnerId int, /* -2: All Owner, -1: No Order Goods, x:OwnerId */
	@UserID int,
	@TerritoryID int
AS

SELECT
	[BulkOrders].[BulkOrderID],
	[OrderDate],
	[Type],
	[ProductCode],
	[BulkOrders].[Description],
	[Color],
	[StartDate],
	[TotalQtyOrdered],
	[TotalQtyCompleted],
	[TotalQtyInProgressBelow50P],
	[TotalQtyInProgressAbove50P],
	[EstimatedDate],
	CASE [Type]
		WHEN 1 THEN
			([TotalQtyOrdered] - ([TotalQtyCompleted]+ISNULL([TotalQtyInProgressBelow50P],0)+ISNULL([TotalQtyInProgressAbove50P], 0)))
		ELSE
			NULL
	END NotStarted,
	[StockLastCheckedDate],
	[StockLastCheckedQty],
	[FOB],
	[CompletedDate],
	[BulkOrders].[SupplierCode],
	[Owner],
	[Catalogue],
	[Page],
	[Pack],
	[PackQty],
	[BulkOrders].[CatalogID],
	Catalogs.CatalogName,
	SalesDescription1,
	SalesDescription2,
	Target,
	[AvailableInUSA],
	[BONo],
	[MinOrderQty],
	[TallPaletQty],	
	([TotalQtyOrdered]) as ActualStock,
	[CatPackDesc],
	[MUKTHGCatPackQty],
	[MUKTHGCatPackDesc],
	--[HomeDelivered15Price],
	[IsShownInLGDCatalog],
	[SpecialOfferDisc],
	([TotalQtyCompleted] - ISNULL((SELECT SUM(QtyIssued)
					FROM DeliveredOrders 
					WHERE [BulkOrders].[BulkOrderID] = DeliveredOrders.[BulkOrderID]), 0)) as QuantityAvailable,
	(SELECT SUM(ReservedQty) FROM CustomerProducts WHERE [BulkOrders].[ProductCode] = CustomerProducts.[ProductCode]) as QuantityReserved,
	/*
	([TotalQtyOrdered]) as ActualStock,
	
	([TotalQtyCompleted] - ISNULL((SELECT SUM(QtyIssued)
					FROM DeliveredOrders 
					WHERE [BulkOrders].[BulkOrderID] = DeliveredOrders.[BulkOrderID]), 0)) as QuantityAvailable,
					
	(([TotalQtyCompleted] - ISNULL((SELECT SUM(QtyIssued)
					FROM DeliveredOrders 
					WHERE [BulkOrders].[BulkOrderID] = DeliveredOrders.[BulkOrderID]), 0)) 
					+ 
					(ISNULL((SELECT SUM(Amount) 
							FROM AdjustQuantity 
							WHERE [BulkOrders].[BulkOrderID] = AdjustQuantity.[BulkOrderID]), 0
							)
					 )
					 ) as AdjustedQuantity,
					 
	(SELECT SUM(ReservedQty) FROM CustomerProducts WHERE [BulkOrders].[ProductCode] = CustomerProducts.[ProductCode]) as QuantityReserved
	*/
	[BulkOrderTerritories].HomeDelivered15Price,
	Currency.Code AS CurrencyCode,
	IDSSorts.[Name] AS IDSSort
FROM
	[BulkOrders]  
	LEFT JOIN [BulkOrderTerritories] ON [BulkOrders].BulkOrderID = [BulkOrderTerritories].BulkOrderID
	LEFT JOIN Suppliers ON [BulkOrders].SupplierCode = Suppliers.SupplierCode
	LEFT JOIN IDSSorts ON IDSSorts.IDSSortID = [BulkOrders].IDSSortID
	LEFT JOIN Catalogs ON Catalogs.CatalogID = [BulkOrders].CatalogID
	LEFT JOIN Currency ON Currency.CurrencyID = [BulkOrderTerritories].CurrencyID
WHERE (@ConPointID = -1 OR Suppliers.ConPointID = @ConPointID)
AND (@IsUSUser = 0 OR (@IsUSUser=1 AND [AvailableInUSA] = 1))
AND (([BulkOrders].Owner IN (SELECT OfficeID FROM UserOffices WHERE UserID=@UserID))
		AND (@OwnerId = -2 
				OR (@OwnerId = -1 AND (type=2 OR type=3)) /* stock fact, stock clear*/
				OR (@OwnerId >= 0 AND Type=1 AND [BulkOrders].Owner = @OwnerId)
			)
	)
AND ([CompletedDate] IS NULL)
AND (@idsSort = -1 OR [BulkOrders].IDSSortID=@idsSort)
AND (([TotalQtyCompleted] - ISNULL((SELECT SUM(QtyIssued)
					FROM DeliveredOrders 
					WHERE [BulkOrders].[BulkOrderID] = DeliveredOrders.[BulkOrderID]), 0)) > @quantity
	)
AND (@TerritoryID = -1 OR [BulkOrderTerritories].TerritoryID = @TerritoryID)


ORDER BY IDSSorts.SortOrder, Type ASC, ProductCode, Catalogs.CatalogOrder ASC, [Page] ASC 

GO

	
 